//
//  SRSpeakingDatabaseManager


#import "SRSpeakingDatabaseManager.h"

#import <FMDB/FMDB.h>
#import "SRCommonConstants.h"

NSString *kTableKeyDataID = @"data_id";
NSString *kTableKeyUserID = @"user_id";
NSString *kTableKeyBookID = @"book_id";

@implementation SRSpeakingDatabaseManager

static NSString *SRSpeadingTestTableName = @"SRSpeakingTestTable";
static NSString *SRSpeakingTestDatabaseName = @"SRSpeakingTestDBName";
static NSString *SRSpeadingTestUploadTableName = @"SRSpeakingTestUploadTable";//上传录音记录，记录分享连接
static NSString *SRBookTableName = @"SRBookTableName";
static NSString *SRHomeworkProblemTableName = @"SRHomeworkProblemTableName";

+ (FMDatabaseQueue *)database {
    static FMDatabaseQueue *dbQueue;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        NSString *dbName = [NSString stringWithFormat:@"/%@.db", SRSpeakingTestDatabaseName];
        NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *docDir = [paths objectAtIndex:0];
        NSFileManager *fileManager = [NSFileManager defaultManager];
        NSString *databaseDir = [docDir stringByAppendingPathComponent:SRSpeakingTestDatabaseName];
        if (![fileManager fileExistsAtPath:databaseDir]) {
            [fileManager createDirectoryAtPath:databaseDir withIntermediateDirectories:YES attributes:nil error:nil];
        }
        NSString *dbPath = [databaseDir stringByAppendingString:dbName];
        
        dbQueue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
        __block BOOL success;
        [dbQueue inDatabase:^(FMDatabase *db) {
            success = [db executeUpdate:[NSString stringWithFormat:
                                         @"CREATE TABLE if not exists %@ \
                                         (user_id TEXT, \
                                         data_id TEXT PRIMARY KEY, \
                                         book_id TEXT, \
                                         page_id TEXT, \
                                         total_sentences_count INTEGER, \
                                         finished_sentences_count INTEGER, \
                                         finished INTEGER, \
                                         create_time INTEGER, \
                                         mp3name TEXT, \
                                         total_score INTEGER, \
                                         accuracy_score INTEGER, \
                                         integrity_score INTEGER, \
                                         fluency_score INTEGER, \
                                         sentences_score TEXT, \
                                         words_score TEXT);", SRSpeadingTestTableName]];
            
            success = [db executeUpdate:[NSString stringWithFormat:
                                         @"CREATE TABLE if not exists %@ \
                                         (user_id TEXT, \
                                         data_id TEXT PRIMARY KEY, \
                                         book_id TEXT, \
                                         page_id TEXT, \
                                         track_id TEXT, \
                                         audio_name TEXT, \
                                         total_score INTEGER, \
                                         share_url TEXT);", SRSpeadingTestUploadTableName]];
            
            success = [db executeUpdate:[NSString stringWithFormat:
                                         @"CREATE TABLE if not exists %@ \
                                         (user_id TEXT, \
                                         book_id TEXT PRIMARY KEY, \
                                         name TEXT, \
                                         zip TEXT, \
                                         grade_id TEXT, \
                                         pic TEXT, \
                                         downloadState INTEGER, \
                                         lastPageID TEXT, \
                                         lastPageIndex INTEGER, \
                                         dowloadProgress REAL);", SRBookTableName]];
            
            success = [db executeUpdate:[NSString stringWithFormat:
                                         @"CREATE TABLE if not exists %@ \
                                         (problem_id TEXT PRIMARY KEY, \
                                         user_id TEXT, \
                                         createTime INTEGER, \
                                         task_id TEXT, \
                                         title TEXT, \
                                         description TEXT, \
                                         pic TEXT, \
                                         audio TEXT, \
                                         audio_timelen INTEGER, \
                                         answer TEXT, \
                                         ctype INTEGER, \
                                         user_answer TEXT, \
                                         answer_pic TEXT, \
                                         learnTime REAL, \
                                         isPlayFinished INTEGER, \
                                         playProgress REAL);", SRHomeworkProblemTableName]];
        }];
        if (!success) {
            dbQueue = nil;
        } else {
//            if (![self checkAndAddColumn:dbQueue]) {
//                dbQueue = nil;
//            }
        }
    });
    return dbQueue;
}

+ (BOOL)saveMyUnpublishedSpeakingModel:(SRSpeakingTestModel *)model {
    FMDatabaseQueue *database = [self database];
    if (database) {
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            NSString *updateString = [NSString stringWithFormat:@"INSERT OR Replace INTO %@(data_id, book_id, page_id, user_id, total_sentences_count, finished_sentences_count, finished, create_time,  mp3name, total_score, accuracy_score, integrity_score, fluency_score, sentences_score, words_score) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", SRSpeadingTestTableName];
            success = [db executeUpdate:updateString, model.data_id, model.book_id, model.page_id, model.user_id, model.totalSentencesCount, model.finishedSentencesCount, model.finished, model.createTime, model.mp3name, model.totalScore, model.accuracyScore, model.integrityScore, model.fluencyScore, model.sentencesScore, model.wordsScore];
        }];
        return success;
    }
    return NO;
}

+ (BOOL)removeMyDraftSpeakingModelWithDataID:(NSString *)dataID {
    FMDatabaseQueue *database = [self database];
    __block BOOL success;
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?", SRSpeadingTestTableName, kTableKeyDataID];
            success = [db executeUpdate:removeString, dataID];
        }];
        return success;
    }
    return NO;
}

//+ (BOOL)removeMyDraftDubbingModel:(SRSpeakingTestModel *)model {
//    FMDatabaseQueue *database = [self database];
//    __block BOOL success;
//    if (database) {
//        [database inDatabase:^(FMDatabase *db) {
//            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE user_id = ? AND course_id = ? AND finished = ?", SRSpeadingTestTableName];
//            success = [db executeUpdate:removeString, model.userId, model.courseId, model.finished];
//        }];
//        return success;
//    }
//    return NO;
//}

+ (BOOL)removeMyUnpublishedSpeakingModel:(SRSpeakingTestModel *)model {
//    FMDatabaseQueue *database = [self database];
//    __block BOOL success;
//    if (database) {
//        [database inDatabase:^(FMDatabase *db) {
//            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE identifier = ?", SRSpeadingTestTableName];
//            success = [db executeUpdate:removeString, model.identifier];
//        }];
//        return success;
//    }
//    return NO;
    return [SRSpeakingDatabaseManager removeMyUnpublishedSpeakingModelWithDataID:model.data_id];
}

+ (BOOL)removeMyUnpublishedSpeakingModelWithDataID:(NSString *)identifier {
    FMDatabaseQueue *database = [self database];
    __block BOOL success;
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?", SRSpeadingTestTableName, kTableKeyDataID];
            success = [db executeUpdate:removeString, identifier];
        }];
        return success;
    }
    return NO;
}

+ (BOOL)removeMyUnpublishedSpeakingModels:(NSArray *)models {
    FMDatabaseQueue *database = [self database];
    __block BOOL success;
    if (database) {
        __block NSMutableArray *idArray = [[NSMutableArray alloc] init];
        [models enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
            SRSpeakingTestModel *model = (SRSpeakingTestModel *)obj;
            [idArray addObject:[NSString stringWithFormat:@"'%@'", model.data_id]];
        }];
        NSString *idString = [idArray componentsJoinedByString:@","];
        [database inDatabase:^(FMDatabase *db) {
            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ IN (%@)", SRSpeadingTestTableName, kTableKeyDataID, idString];
            success = [db executeUpdate:removeString];
        }];
        return success;
    }
    return NO;
}

+ (NSArray *)searchMyUnpublishedSpeakingModel:(NSString *)userId finished:(BOOL)finished {
    if (!userId || userId.length == 0) {
        return @[];
    }
    NSAssert(userId.length > 0, @"input must be valid.");
    FMDatabaseQueue *database = [self database];
    
    NSMutableArray *retval = [NSMutableArray array];
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *getDataString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? AND finished = ? ORDER BY create_time", SRSpeadingTestTableName, kTableKeyUserID];
            FMResultSet *results = [db executeQuery:getDataString, userId, @(finished)];
            if (results) {
                while ([results next]) {
                    NSDictionary *resultDictionary = [results resultDictionary];
                    SRSpeakingTestModel *model = [MTLJSONAdapter modelOfClass:[SRSpeakingTestModel class] fromJSONDictionary:resultDictionary error:nil];
                    if (model) {
                        [retval insertObject:model atIndex:0];
                    }
                    
                }
            }
        }];
    }
    return [NSArray arrayWithArray:retval];
}

+ (NSArray *)searchMyDraftModelWithDataID:(NSString *)dataID {
    if (!dataID || dataID.length == 0) {
        return nil;
    }
    NSAssert(dataID.length > 0, @"input must be valid");
    FMDatabaseQueue *database = [self database];
    
    NSMutableArray *retval = [NSMutableArray array];
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *getDataString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", SRSpeadingTestTableName, kTableKeyDataID];
            FMResultSet *results = [db executeQuery:getDataString, dataID];
            if (results) {
                while ([results next]) {
                    NSDictionary *resultDictionary = [results resultDictionary];
//                    NSLog(@"searchMyDraftModelWithDataID:%@", resultDictionary);
                    SRSpeakingTestModel *model = [MTLJSONAdapter modelOfClass:[SRSpeakingTestModel class] fromJSONDictionary:resultDictionary error:nil];
                    if (model) {
                        [retval insertObject:model atIndex:0];
                    }
                }
            }
        }];
    }
    return [NSArray arrayWithArray:retval];
}

#pragma mark - Sentence Upload 


+ (BOOL)saveSpeakingTrackModel:(SRBookPageTrackItemModel *)model fileName:(NSString *)mp3Name shareUrl:(NSString *)shareURl {
    FMDatabaseQueue *database = [self database];
    if (database) {
        NSNumber *score = @(0);
        NSString *userID = [SRLoginUser userID];
        if (!userID) {
            userID = kSRNotLoginDefaultUserID;
        }
        
        if (model.scoreModel) {
            score = model.scoreModel.totalScore;
        }
        
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            NSString *updateString = [NSString stringWithFormat:@"INSERT INTO %@(data_id, book_id, page_id, user_id, track_id, audio_name, total_score, share_url) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", SRSpeadingTestUploadTableName];
            success = [db executeUpdate:updateString, model.data_id, model.book_id, model.page_id, userID, model.track_id, mp3Name, score, shareURl];
        }];
        return success;
    }
    return NO;
}

+ (BOOL)removeSpeakingTrackModelWithDataID:(NSString *)dataID {
    FMDatabaseQueue *database = [self database];
    __block BOOL success;
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?", SRSpeadingTestUploadTableName, kTableKeyDataID];
            success = [db executeUpdate:removeString, dataID];
        }];
        return success;
    }
    return NO;
}

+ (NSArray *)searchSpeakingTrackModelWithDataID:(NSString *)dataID {
    if (!dataID || dataID.length == 0) {
        return nil;
    }
    FMDatabaseQueue *database = [self database];
    
    NSMutableArray *retval = [NSMutableArray array];
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *getDataString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", SRSpeadingTestUploadTableName, kTableKeyDataID];
            FMResultSet *results = [db executeQuery:getDataString, dataID];
            if (results) {
                while ([results next]) {
                    NSDictionary *resultDictionary = [results resultDictionary];
                    SRBookPageTrackItemModel *model = [MTLJSONAdapter modelOfClass:[SRBookPageTrackItemModel class] fromJSONDictionary:resultDictionary error:nil];
                    if (model) {
                        [retval insertObject:model atIndex:0];
                    }
                }
            }
        }];
    }
    return [NSArray arrayWithArray:retval];
}

#pragma mark - Book Info


+ (BOOL)saveBookModel:(FZHomeCourseModel *)model {
    FMDatabaseQueue *database = [self database];
    if (database) {
        NSString *userID = [SRLoginUser userID];
        if (!userID) {
            userID = kSRNotLoginDefaultUserID;
        }
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            NSString *updateString = [NSString stringWithFormat:@"INSERT INTO %@(user_id, book_id, name, zip, grade_id, pic, downloadState, lastPageID, lastPageIndex, dowloadProgress) VALUES('%@', '%@', '%@', '%@', '%@', '%@', %lu, '%@', %ld, %f)", SRBookTableName, userID, model.identifier, model.title, model.zip, model.gradeID, model.pic, (unsigned long)model.downloadState, model.lastPageID, (long)model.lastPageIndex, model.dowloadProgress];
            success = [db executeUpdate:updateString];
        }];
        return success;
    }
    return NO;
}

+ (BOOL)updateBookModel:(NSString *)bookID pageIndex:(NSInteger )pageIndex pageID:(NSString *)pageID {
    FMDatabaseQueue *database = [self database];
    if (database) {
//        NSString *userID = [SRLoginUser userID];
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            NSString *updateString = [NSString stringWithFormat:@"UPDATE %@ SET lastPageIndex=%d, lastPageID='%@' WHERE %@ = ?", SRBookTableName, (int)pageIndex, pageID, kTableKeyBookID];

            success = [db executeUpdate:updateString, bookID];
        }];
        return success;
    }
    return NO;
}

+ (BOOL)removeBookModelWithBookID:(NSString *)book_id {
    FMDatabaseQueue *database = [self database];
    __block BOOL success;
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?", SRBookTableName, kTableKeyBookID];
            success = [db executeUpdate:removeString, book_id];
        }];
        return success;
    }
    return NO;
}

+ (NSArray *)searchBookModelWithBookID:(NSString *)dataID {
    if (!dataID || dataID.length == 0) {
        return nil;
    }
    FMDatabaseQueue *database = [self database];
    
    NSMutableArray *retval = [NSMutableArray array];
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *getDataString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", SRBookTableName, kTableKeyBookID];
            FMResultSet *results = [db executeQuery:getDataString, dataID];
            if (results) {
                while ([results next]) {
                    NSDictionary *resultDictionary = [results resultDictionary];
                    FZHomeCourseModel *model = [MTLJSONAdapter modelOfClass:[FZHomeCourseModel class] fromJSONDictionary:resultDictionary error:nil];
                    if (model) {
                        [retval insertObject:model atIndex:0];
                    }
                }
            }
        }];
    }
    return [NSArray arrayWithArray:retval];
}

#pragma mark - Audio

+ (BOOL)saveAudioModel:(SRHomeworkPictureModel *)model {
    FMDatabaseQueue *database = [self database];
    NSString *userID = [SRLoginUser userID];
        //    NSString *emptyString = @"";
    NSTimeInterval timestamp = [NSDate timestamp];
    NSNumber *createTime = [NSNumber numberWithLongLong:timestamp];
    NSNumber *isPlayFinished = [NSNumber numberWithBool:model.isPlayFinished];
    NSNumber *playProgress = [NSNumber numberWithFloat:model.playProgress];
    NSNumber *ctype = [NSNumber numberWithInteger:model.ctype];
    NSNumber *learnTime = [NSNumber numberWithFloat:model.learnTime];

    if (database) {
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            
            NSString *updateString = [NSString stringWithFormat:@"INSERT or replace INTO %@(problem_id, user_id, createTime, task_id, title, description, pic, audio, audio_timelen, answer, ctype, user_answer,answer_pic,learnTime, isPlayFinished, playProgress) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", SRHomeworkProblemTableName];
            success = [db executeUpdate:updateString, model.problem_id, userID, createTime, model.task_id, model.title, model.desc, model.pic, model.audio, model.audio_timelen, model.answer, ctype, model.answerByUser, model.answer_pic,learnTime, isPlayFinished, playProgress];
            
        }];
        return success;
    }
    return NO;
}


+ (BOOL)updateAudioWithID:(NSString *)audioID playProgress:(CGFloat)playProgress{
    FMDatabaseQueue *database = [self database];
        //    NSString *userID = [SRLoginUser userID];
    if (playProgress < 0){
        playProgress = 0;
    } else if (playProgress > 1){
        playProgress = 1;
    }
    
    NSNumber *playProgressNumber = [NSNumber numberWithFloat:playProgress];
    
    if (database) {
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            
            NSString *updateString = [NSString stringWithFormat:@"UPDATE %@ set playProgress=? WHERE %@=?", SRHomeworkProblemTableName, @"problem_id"];
            
            success = [db executeUpdate:updateString, playProgressNumber, audioID];
            
        }];
        return success;
    }
    return NO;
}

+ (BOOL)updateAudioWithID:(NSString *)audioID learnTime:(CGFloat)learnTime{
    FMDatabaseQueue *database = [self database];
        //    NSString *userID = [SRLoginUser userID];

    NSNumber *learnTimeNumber = [NSNumber numberWithFloat:learnTime];
    
    if (database) {
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            
            NSString *updateString = [NSString stringWithFormat:@"UPDATE %@ set learnTime=? WHERE %@=?", SRHomeworkProblemTableName, @"problem_id"];
            
            success = [db executeUpdate:updateString, learnTimeNumber, audioID];
            
        }];
        return success;
    }
    return NO;
}

+ (BOOL)updateAudioWithID:(NSString *)audioID playFinish:(BOOL)playFinished{
    FMDatabaseQueue *database = [self database];
        //    NSString *userID = [SRLoginUser userID];
    NSNumber *playFinishedNumber = [NSNumber numberWithBool:playFinished];
    
    if (database) {
        __block BOOL success;
        [database inDatabase:^(FMDatabase *db) {
            
            NSString *updateString = [NSString stringWithFormat:@"UPDATE %@ set isPlayFinished=? WHERE %@=?", SRHomeworkProblemTableName, @"problem_id"];
            
            success = [db executeUpdate:updateString, playFinishedNumber, audioID];
            
        }];
        return success;
    }
    return NO;
}

+ (BOOL)removeAudioModelWithDataID:(NSString *)dataID {
    FMDatabaseQueue *database = [self database];
    __block BOOL success;
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *removeString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?", SRHomeworkProblemTableName, @"problem_id"];
            success = [db executeUpdate:removeString, dataID];
        }];
        return success;
    }
    return NO;
}

+ (NSArray *)searchAudioModelWithDataID:(NSString *)dataID {
    if (!dataID || dataID.length == 0) {
        return nil;
    }
    NSAssert(dataID.length > 0, @"input must be valid");
    FMDatabaseQueue *database = [self database];
    
    NSMutableArray *retval = [NSMutableArray array];
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *getDataString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?", SRHomeworkProblemTableName, @"problem_id"];
            FMResultSet *results = [db executeQuery:getDataString, dataID];
            if (results) {
                while ([results next]) {
                    NSDictionary *resultDictionary = [results resultDictionary];
                        //                    NSLog(@"searchMyDraftModelWithDataID:%@", resultDictionary);
                    SRHomeworkPictureModel *model = [MTLJSONAdapter modelOfClass:[SRHomeworkPictureModel class] fromJSONDictionary:resultDictionary error:nil];
                    if (model) {
                        [retval insertObject:model atIndex:0];
                    }
                }
            }
        }];
    }
    return [NSArray arrayWithArray:retval];
}

+ (NSArray *)searchAudioModels{
    FMDatabaseQueue *database = [self database];
    
    NSMutableArray *retval = [NSMutableArray array];
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *getDataString = [NSString stringWithFormat:@"SELECT * FROM %@", SRHomeworkProblemTableName];
            FMResultSet *results = [db executeQuery:getDataString];
            if (results) {
                while ([results next]) {
                    NSDictionary *resultDictionary = [results resultDictionary];
                        //                    NSLog(@"searchMyDraftModelWithDataID:%@", resultDictionary);
                    SRHomeworkPictureModel *model = [MTLJSONAdapter modelOfClass:[SRHomeworkPictureModel class] fromJSONDictionary:resultDictionary error:nil];
                    if (model) {
                        [retval insertObject:model atIndex:0];
                    }
                }
            }
        }];
    }
    return [NSArray arrayWithArray:retval];
}

+ (NSArray *)searchAudioModelsWithTaskID:(NSString *)taskID{
    FMDatabaseQueue *database = [self database];
    
    NSMutableArray *retval = [NSMutableArray array];
    if (database) {
        [database inDatabase:^(FMDatabase *db) {
            NSString *getDataString = [NSString stringWithFormat:@"SELECT * FROM %@  WHERE %@ = ?", SRHomeworkProblemTableName, @"task_id"];
            FMResultSet *results = [db executeQuery:getDataString, taskID];
            if (results) {
                while ([results next]) {
                    NSDictionary *resultDictionary = [results resultDictionary];
                        //                    NSLog(@"searchMyDraftModelWithDataID:%@", resultDictionary);
                    SRHomeworkPictureModel *model = [MTLJSONAdapter modelOfClass:[SRHomeworkPictureModel class] fromJSONDictionary:resultDictionary error:nil];
                    if (model) {
                        [retval insertObject:model atIndex:0];
                    }
                }
            }
        }];
    }
    return [NSArray arrayWithArray:retval];
}

@end
